﻿Public Class AddNewUserForm
    Dim cnn As New OleDb.OleDbConnection

    Private Sub ComboBox1_DropDown(sender As System.Object, e As System.EventArgs) Handles cboAge.DropDown

        For Index = 17 To 80
            cboAge.Items.Add(Index)
        Next
    End Sub

    Private Sub AddNewUserForm_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'User_LogDataSet.UserLog' table. You can move, or remove it, as needed.
        cnn = New OleDb.OleDbConnection
        cnn.ConnectionString = "Provider=Microsoft.ace.oledb.12.0; data source=C:\Users\DJWalshy\Documents\User_Log.accdb"
        '
        'get data into list
        Me.RefreshData()

    End Sub

    Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
        Me.Close()

    End Sub

    Private Sub btnClear_Click(sender As System.Object, e As System.EventArgs) Handles btnClear.Click
        Me.txtFirstName.Text = ""
        Me.txtFlight.Text = ""
        Me.txtLastName.Text = ""
        Me.cboAge.Text = ""
        Me.cboSex.Text = ""
        'enable button edit
        Me.btnEdit.Enabled = True
        'set button add to add label
        Me.btnAdd.Text = "add"
        '
    End Sub

    Private Sub RefreshData()
        If Not cnn.State = ConnectionState.Open Then
            cnn.Open()
        End If

        Dim da As New OleDb.OleDbDataAdapter("SELECT FirstName as [First_Name], LastName, Age, Sex, Flight", cnn)

        Dim dt As New DataTable
        'fill data to datatable
        da.Fill(dt)

        'offer data in datatable into datagridview
        Me.dgvData.DataSource = dt

    End Sub

    Private Sub btnAdd_Click(sender As System.Object, e As System.EventArgs) Handles btnAdd.Click
        Dim cmd As New OleDb.OleDbCommand
        'check if connection is open, if not then open it
        If Not cnn.State = ConnectionState.Open Then
            cnn.Open()
        End If

        cmd.Connection = cnn
        'check whether add new or update

        'add new
        'add data to table

        cmd.CommandText = "INSERT INTO UserLog(First_Name, Last_Name, Age, Sex, Flight) " & _
                          " VALUES(" & Me.txtFirstName.Text & ",'" & Me.txtLastName.Text & "','" & _
                          Me.cboAge.Text & "','" & Me.cboSex.Text & "','" & Me.txtFlight.Text & "')"
        cmd.ExecuteNonQuery()

        'update data in the table
        cmd.CommandText = "UPDATE UserLog " & _
            " SET First_Name=" & Me.txtFirstName.Text & _
            ", Last_Name='" & Me.txtLastName.Text & "'" & _
            ", Age='" & Me.cboAge.Text & "'" & _
            ", Sex='" & Me.cboSex.Text & "'" & _
            ", Flight='" & Me.txtFlight.Text & "'" & _
            " WHERE First_Name=" & Me.txtFirstName.Text

        cmd.ExecuteNonQuery()

        'refresh data in the list
        RefreshData()
        'clear form
        Me.btnClear.PerformClick()

        'close connection
        cnn.Close()
    End Sub

    Private Sub btnEdit_Click(sender As System.Object, e As System.EventArgs) Handles btnEdit.Click
        'check for the selected item in list
        If Me.dgvData.SelectedRows.Count > 0 Then
            If Me.dgvData.SelectedRows.Count > 0 Then
                Dim intmbrID As Integer = Me.dgvData.SelectedRows(0).Cells("id").Value
                'get data from database followed by member id
                'open connection
                If Not cnn.State = ConnectionState.Open Then
                    cnn.Open()
                End If
                'get data into datatable
                Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM UserLog " & _
                                                     " WHERE mbrid=" & intmbrID, cnn)
                Dim dt As New DataTable
                da.Fill(dt)


                Me.txtFirstName.Text = dt.Rows(0).Item("First_Name")
                Me.txtLastName.Text = dt.Rows(0).Item("Last_Name")
                Me.cboAge.Text = dt.Rows(0).Item("Age")
                Me.cboSex.Text = dt.Rows(0).Item("Sex")
                Me.txtFlight.Text = dt.Rows(0).Item("Flight")

                'hide the id to be edited in TAG of txtmbrid in case id is changed

                'change button add to update
                Me.btnAdd.Text = "Update"
                'disable button edit
                Me.btnEdit.Enabled = False
                'close connection
                cnn.Close()

            End If
        End If
    End Sub

    Private Sub btnDelete_Click(sender As System.Object, e As System.EventArgs) Handles btnDelete.Click
        'check for the selected item in list
        If Me.dgvData.SelectedRows.Count > 0 Then
            If Me.dgvData.SelectedRows.Count > 0 Then
                Dim intmbrID As Integer = Me.dgvData.SelectedRows(0).Cells("id").Value
                'open connection
                If Not cnn.State = ConnectionState.Open Then
                    cnn.Open()
                End If

                'delete data
                Dim cmd As New OleDb.OleDbCommand
                cmd.Connection = cnn
                cmd.CommandText = "DELETE FROM UserLog WHERE mbrid=" & intmbrID
                cmd.ExecuteNonQuery()
                'refresh data
                Me.RefreshData()

                'close connection
                cnn.Close()


            End If
        End If
    End Sub
End Class